package com.nextive.weightlogger.dao;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.nextive.weightlogger.dto.WeightEntry;
import com.nextive.weightlogger.util.DateUtil;



import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Data access object to read, save and delete weight entries in a Sqlite
 * database.
 * 
 * @author Federico Baseggio <fedebaseggio@gmail.com>
 */
public class WeightLogDao {
	private static final String TABLE = "weight_log";
	private static final int DB_VERSION = 2;
	private static final String FIELD_ID = "_id";
	private static final String FIELD_WEIGHT = "weight_grams";
	private static final String FIELD_TIMESTAMP = "timestamp";
	private static final String CREATE_STATEMENT = "CREATE TABLE " + TABLE
			+ "(" + FIELD_ID + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
			+ FIELD_TIMESTAMP + " INTEGER NOT NULL, " + FIELD_WEIGHT
			+ " REAL NOT NULL);";
	private static final String DROP_STATEMENT = "DROP TABLE IF EXISTS "
			+ TABLE + ";";
	
	private DbHelper dbHelper;
	
	/**
	 * Constructor.
	 * 
	 * @param context
	 *            A context, usually the activity that invokes this constructor.
	 */
	public WeightLogDao(Context context)
	{
		dbHelper = new DbHelper(context);
	}
	
	/**
	 * Manages the creation and upgrade of the database.
	 * 
	 * @author Federico Baseggio <fedebaseggio@gmail.com>
	 */
	private class DbHelper extends SQLiteOpenHelper {
		/**
		 * Constructor.
		 * 
		 * @param context
		 *            A context used to open or create the database.
		 */
		public DbHelper(Context context) {
			super(context, TABLE, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_STATEMENT);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL(DROP_STATEMENT);
			db.execSQL(CREATE_STATEMENT);
		}
	}
	
	/**
	 * Adds a new weight entry.
	 * 
	 * @param weightEntry The weight entry to add.
	 * @return True if insert succeeded, false if an error occurred.
	 */
	public boolean add(WeightEntry weightEntry)
	{
		SQLiteDatabase db = null;
		try {
			ContentValues values = new ContentValues();
			values.put(FIELD_TIMESTAMP, DateUtil.toUnixTime(weightEntry
							.getDateTime()));
			values.put(FIELD_WEIGHT, weightEntry.getWeightGrams());
			db = dbHelper.getWritableDatabase();
			long id = db.insert(TABLE, null, values);
			weightEntry.setId(id);
			return id != -1;
		}
		finally
		{
			db.close();
		}
	}

	/**
	 * Updated a new weight entry.
	 * 
	 * @param weightEntry
	 *            The weight entry to update. The id is used to match the
	 *            existing entry in the database and the remaining fields are
	 *            used to update the entry in the database.
	 */
	public long update(WeightEntry weightEntry)
	{
		SQLiteDatabase db = null;
		try {
			ContentValues values = new ContentValues();
			values.put(FIELD_TIMESTAMP, Math.floor(weightEntry.getDateTime()
					.getTimeInMillis() / 1000));
			values.put(FIELD_WEIGHT, weightEntry.getWeightGrams());
			db = dbHelper.getWritableDatabase();
			return db.update(TABLE, values, FIELD_ID + " = ?",
					new String[] { String.valueOf(weightEntry.getId()) });
		}
		finally
		{
			db.close();
		}
	}

	/**
	 * Deletes the entry with the specified id from the database.
	 * 
	 * @param id The id of the entry to delete.
	 * @return The amount of rows deleted.
	 */
	public long delete(long id)
	{
		SQLiteDatabase db = null;
		try {
			db = dbHelper.getWritableDatabase();
			return db.delete(TABLE, FIELD_ID + " = ?",
					new String[] { String.valueOf(id) });
		}
		finally
		{
			db.close();
		}
	}

	/**
	 * Retrieves a weight entry from the database.
	 * 
	 * @param id
	 *            The id of the entry to retrieve.
	 * @return The weight entry with the specified id, or null if not found.
	 */
	public WeightEntry read(long id)
	{
		String where = FIELD_ID + " = ?";
		String[] args = new String[]{String.valueOf(id)};
		List<WeightEntry> results = read(where, args, null);
		return results.size() == 0 ? null : results.get(0);
	}
	
	/**
	 * Retrieves the weight entry with the highest date/time in the database.
	 * 
	 * @return The weight entry with the highest date/time in the database.
	 */
	public WeightEntry readMostRecent() {
		String where = FIELD_TIMESTAMP + " = (SELECT MAX(" + FIELD_TIMESTAMP
				+ ") FROM " + TABLE + ")";
		List<WeightEntry> results = read(where, new String[] {}, 1L);
		return results.size() == 0 ? null : results.get(0);
	}
	
	/**
	 * Retrieves all entries from a specified date/time on.
	 * 
	 * @param dateTime The date/time from which entries will be selected on.
	 * @return The entries from a specified date/time on.
	 */
	public List<WeightEntry> readFrom(Calendar dateTime)
	{
		String where = FIELD_TIMESTAMP + " >= ?";
		String[] args = new String[]{String.valueOf(DateUtil.toUnixTime(dateTime))};
		List<WeightEntry> results = read(where, args, null);
		return results;
	}
	
	/**
	 * Retrieves all entries in the database.
	 * 
	 * @return All entries in the database.
	 */
	public List<WeightEntry> readAll()
	{
		return read(null, null, null);
	}
	
	/**
	 * Retrieves all entries that match the specified where clause.
	 * 
	 * @param where The Sqlite where clause used to filter results.
	 * @param args The values of the parameterized arguments in the where clause.
	 * @param maxResults How many entries to retrieve at most.
	 * @return All entries that match the specified where clause.
	 */
	private List<WeightEntry> read(String where, String[] args, Long maxResults)
	{
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			db = dbHelper.getReadableDatabase();
			String limit = maxResults == null ? null : "0, " + String.valueOf(maxResults);
			String[] selectColumns = new String[] { FIELD_ID, FIELD_TIMESTAMP,
					FIELD_WEIGHT };
			cursor = db.query(TABLE, selectColumns, where, args, null, null,
					FIELD_TIMESTAMP + " DESC", limit);
			return toWeightEntryList(cursor);
		} finally {
			try
			{
				cursor.close();
			}
			finally
			{
				db.close();
			}
		}
	}
	
	/**
	 * Retrieves the weight entries from the cursor.
	 * 
	 * @param cursor The database cursor.
	 * @return The entries read from the cursor.
	 */
	private List<WeightEntry> toWeightEntryList(Cursor cursor)
	{
		List<WeightEntry> list = new ArrayList<WeightEntry>();
		while (cursor.moveToNext()) {
			WeightEntry weightEntry = new WeightEntry();
			weightEntry.setId(cursor.getLong(cursor
					.getColumnIndex(FIELD_ID)));
			weightEntry.setWeightGrams(cursor.getFloat(cursor
					.getColumnIndex(FIELD_WEIGHT)));
			long unixTime = cursor.getLong(cursor
					.getColumnIndex(FIELD_TIMESTAMP));
			weightEntry.setDateTime(DateUtil.fromUnixTime(unixTime));
			list.add(weightEntry);
		}
		return list;
	}

	/**
	 * Retrieves the total amount of entries in the database.
	 * 
	 * @return The total amount of entries in the database.
	 */
	public long readEntryCount()
	{
		SQLiteDatabase db = null;
		Cursor cursor = null;
		long count = 0;
		try {
			db = dbHelper.getReadableDatabase();
			cursor = db
					.rawQuery("SELECT COUNT(*) rowCount FROM " + TABLE, null);
			cursor.moveToNext();
			count = cursor.getLong(0);
			return count;
		} finally {
			try {
				cursor.close();
			} finally {
				db.close();
			}
		}
	}	
}
